#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
This module defines SQLite3-based database. It's used to keep all
task/tag definitions.
"""

__all__ = [ 'Database' ]

import os
import sqlite3
import tatolib.errors   as errors
import tatolib.taskobj  as taskobj
import tatolib.strutil  as strutil
import tatolib.timeutil as timeutil
import traceback

V1_TABLE_CONFIG       = "config"
V1_TABLE_TASKS        = "tasks"
V1_TABLE_TAGS         = "tags"
V1_TABLE_TASK_TAG_MAP = "tasktagmap"

V1_ATTR_CONFIG = "dbversion"
V1_ATTR_TASKS  = "name, detail, due, complete, flags"
V1_ATTR_TAGS   = "name, detail, weighting, nameid"
V1_ATTR_TASK_TAG_MAP = "taskid, tagid"

V1_DT_TBD_PLACEHOLDER = strutil.u_("0000/00/00")

V1_CONFIG_CREATE_TABLE = strutil.u_("create table config (dbversion text)")
V1_TAGS_CREATE_TABLE = strutil.u_(" create table tags (id integer primary key autoincrement, name text, detail text, weighting text, nameid text) """)
V1_TASKS_CREATE_TABLE = strutil.u_("create table tasks (id integer primary key autoincrement, name text, detail text, flags text, due text, complete text) ")
V1_TASK_TAG_MAP_CREATE_TABLE = strutil.u_("create table tasktagmap (taskid integer, tagid integer)")

V1_CONFIG_INIT = strutil.u_("insert into config (dbversion) values ('1')")

V1_INSERT_TASK = strutil.u_("insert into tasks (name, detail, flags, due, complete) values (?,?,?,?,?)")
V1_INSERT_TAG = strutil.u_("insert into tags (name, detail, weighting, nameid) values (?,?,?,?)")
V1_INSERT_TASK_TAG_MAP = strutil.u_("insert into tasktagmap (taskid, tagid) values (?,?)")

V1_SELECT_TASK_BY_ID = strutil.u_("select id from tasks where id=?")
V1_SELECT_TAG_BY_ID = strutil.u_("select id from tags where id=?")
V1_SELECT_TAG_WEIGHTING_BY_ID = strutil.u_("select weighting from tags where id=?")
V1_SELECT_TAG_BY_NAMEID = strutil.u_("select id from tags where nameid=?")
V1_SELECT_ALL_TASK = strutil.u_("select id, name, detail, due, flags, complete from tasks")
V1_SELECT_ALL_TAG = strutil.u_("select id, name, detail, weighting from tags")
V1_SELECT_TASK_TAG_MAP_BY_ID = strutil.u_("select taskid, tagid from tasktagmap where taskid=? and tagid=?")
V1_SELECT_TASK_TAG_MAP_BY_TASKID = strutil.u_("select taskid, tagid from tasktagmap where taskid=?")
V1_SELECT_ALL_ONGOING_TASK = strutil.u_("select id, name, detail, due, flags, complete from tasks where complete = '{0}'".format(V1_DT_TBD_PLACEHOLDER))
V1_SELECT_ASSIGNED_TAGS_BY_TASKID = strutil.u_("select tagid from tasktagmap where taskid=?")
V1_SELECT_TASK_DATA_BY_ID = strutil.u_("select name, detail, due, flags, complete from tasks where id=?")
V1_SELECT_TAG_DATA_BY_ID = strutil.u_("select name, detail, weighting from tags where id=?")


V1_UPDATE_TASK_BY_ID = strutil.u_("update tasks set name=?, detail=?, due=?, flags=?, complete=? where id=?")
V1_UPDATE_TAG_BY_ID = strutil.u_("update tags set name=?, detail=?, weighting=?, nameid=? where id=?")

V1_DELETE_TASK_TAG_MAP = strutil.u_("delete from tasktagmap where (taskid=? and tagid=?)")

V1_DELETE_TASK_BY_ID = strutil.u_("delete from tasks where id=?")
V1_DELETE_TAG_BY_ID = strutil.u_("delete from tags where id=?")
V1_DELETE_TASK_TAG_MAP_BY_TASK = strutil.u_("delete from tasktagmap where (taskid=?)")
V1_DELETE_TASK_TAG_MAP_BY_TAG = strutil.u_("delete from tasktagmap where (tagid=?)")

class Database(object):
    "Database to store task objects,based on ndbm."
    def __init__(self, dbfile, log = errors.DoNothingLogger()):
        """
        Database.__init__(self, dbfile)

        Initialize a database based on given database file. If the file
        does not exist, create a new database. If the database file
        exists, the content will be checked to make sure the database is
        well formatted.

        May raise Python standard ValueError or TypeEror exception when
        the database is corrupted or contains invalid data.
        """
        self.__db  = None
        self.__log = log
        db = None
        if dbfile is None:
            db = sqlite3.connect(":memory:")
        else:
            # If given file does not exist, we try to create a
            # new database. However, if the given file exists, it has to
            # be a valid database file.
            if os.path.exists(dbfile):
                if not os.path.isfile(dbfile):
                    self.__log.err("Database is not a file")
                    raise errors.DatabaseError(dbfile, errors.E_DB_NOTFILE)
                self.__db = self.__verify_db(dbfile)
            else:
                self.__db = self.__init_db(dbfile)
        self.__dbfile = dbfile
        return

    def __del__(self):
        """
        Database.__del__(self) -> None

        Close database handle. If current database is stored in memory,
        all information will be lost.
        """
        self.close()

    def __init_db(self, dbfile):
        """
        Database.__init_db(self, dbfile) -> None

        Initialize database.
        """
        db = None
        try:
            db = sqlite3.connect(dbfile)
            cur = db.cursor()
            cur.execute(V1_CONFIG_CREATE_TABLE)
            cur.execute(V1_TAGS_CREATE_TABLE)
            cur.execute(V1_TASKS_CREATE_TABLE)
            cur.execute(V1_TASK_TAG_MAP_CREATE_TABLE)
            cur.execute(V1_CONFIG_INIT)
            db.commit()
            cur.close()
        except sqlite3.OperationalError:
            self.__log.err("The database is not correctly defined")
            self.__log.err("SQLite3: {0}".format(traceback.format_exc()))
            raise errors.DatabaseError(V1_TABLE_CONFIG, errors.E_DB_BADTYPE)
        except Exception:
            self.__log.err("Exception: {0}".format(traceback.format_exc()))
            self.__log.err("Given file may not be a SQLite3 database")
            raise
        return db

    def __verify_db(self, dbfile):
        """
        Database.__verify_db(self, dbfile) -> None

        Try to initialize database. If the database is well defined,
        do nothing.
        """
        db = None
        try:
            db = sqlite3.connect(dbfile)
            self.__log.msg("Open database and query config table")
            cur = db.cursor()
            cur.execute("select {1} from {0}".format(V1_TABLE_CONFIG, \
                                                     V1_ATTR_CONFIG))
            db.commit()
            vals = cur.fetchall()
            if len(vals) != 1:
                self.__log.err("Table 'config' attrs = {0}".format(vals))
                raise errors.DatabaseError(V1_TABLE_CONFIG, \
                                           errors.E_DB_CORRUPTED)
            if vals[0][0] != strutil.u_("1") and vals[0][0] != "1":
                self.__log.err("DB version = {0}".format(vals[0]))
                raise errors.DatabaseError(V1_TABLE_CONFIG, \
                                           errors.E_DB_WRONGVERSION)
            cur.close()
        except sqlite3.OperationalError:
            self.__log.err("SQLite3: {0}".format(traceback.format_exc()))
            self.__log.err("Failed when verifying version number")
            raise errors.DatabaseError(V1_TABLE_CONFIG, errors.E_DB_BADTYPE)
        except Exception as e:
            self.__log.err("Exception: {0}".format(traceback.format_exc()))
            self.__log.err("Given file may not be a SQLite3 database")
            raise
        for tablevals in [(V1_TABLE_TASKS, V1_ATTR_TASKS), \
                           (V1_TABLE_TAGS, V1_ATTR_TAGS), \
                           (V1_TABLE_TASK_TAG_MAP, V1_ATTR_TASK_TAG_MAP)]:
            try:
                self.__log.msg("Verify each table")
                cur = db.cursor()
                cur.execute("select {1} from {0}".format(tablevals[0], \
                                                         tablevals[1]))
                db.commit()
                vals = cur.fetchall()
                cur.close()
            except sqlite3.OperationalError:
                self.__log.err("{0}".format(traceback.format_exc()))
                self.__log.err("Table: {0} undefined".format(tablevals[0]))
                raise errors.DatabaseError(tablevals[0], \
                                           errors.E_DB_TABLEMISSING)
            except Exception as e:
                self.__log.err("Unknown exception: {0}".format(e))
                self.__log.err("Given file may not be a SQLite3 database")
        # Now we confirmed the database is good.
        return db

    def new(self, obj):
        """
        Database.new(self, obj): -> ID

        Create a new entry in database. The type of passed-in object
        indicates the object type.

        Returns an ID if a new entry is created, or -1 if the object
        cannot be created. If added object is a tag and there's already
        a tag object with the same name (case insensitive) defined in
        the database, an TaskObjectError exception is thrown..
        """
        if type(obj) is taskobj.Task:
            strflags = obj.flagstostr()
            strdue = timeutil.dttostr(obj.due)
            strcomplete = None
            if obj.complete is None:
                strcomplete = V1_DT_TBD_PLACEHOLDER
            else:
                strcomplete = timeutil.dttostr(obj.complete)
            values = (obj.name, obj.detail, strflags, strdue, strcomplete)
            cur = self.__db.cursor()
            try:
                cur.execute(V1_INSERT_TASK, values)
                newid = cur.lastrowid
                self.__db.commit()
                return newid
            finally:
                cur.close()
        elif type(obj) is taskobj.Tag:
            nameid = self.__calc_tag_nameid(obj.name)
            cur = self.__db.cursor()
            try:
                # Don't allow two tags with same name appear in a database.
                cur.execute(V1_SELECT_TAG_BY_NAMEID, (nameid,))
                found_tags = cur.fetchall()
                if len(found_tags) != 0:
                    self.__log.err("Tag found with same name: {0}".format(\
                        found_tags[0]))
                    raise errors.TaskObjectError(nameid, \
                                                 errors.E_TA_SAMENAME)
                # Now do adding...
                values = (obj.name, obj.detail, obj.weighting, nameid)
                cur.execute(V1_INSERT_TAG, values)
                newid = cur.lastrowid
                self.__db.commit()
                return newid
            finally:
                cur.close()
        self.__log.err("new(): Unknown object type {0}".format(type(obj)))
        raise errors.TaskObjectError(obj, E_TA_WRONGTYPE)

    def list(self, objtype, completed_only = True):
        """
        Database.list(self, objtype, completed_only = True): -> list

        List all objects stored in database.

        Return a list. Each element in the list has three fields:
          - id
          - object
          - weighting

        For Task objects, the weighting is calculated based on the
        internal algorithm. For Tag object, weighting is just a copy of
        Tag.weighting property.

        The optional parameter, completed_only, takes effect only
        when getting tasks. When it is set to True, the list() function 
        will not return completed tasks.

        If passed objtype is not recognized, a TaskObjectError
        object is thrown.
        """
        retlist = []
        if objtype is taskobj.Task:
            cur = self.__db.cursor()
            try:
                if completed_only:
                    cur.execute(V1_SELECT_ALL_ONGOING_TASK)
                else:
                    cur.execute(V1_SELECT_ALL_TASK)
                found_tasks = cur.fetchall()
                for each_task in found_tasks:
                    objid = int(each_task[0])
                    n = each_task[1]
                    d = each_task[2]
                    u = timeutil.strtoutcday(each_task[3])
                    f = taskobj.Task.strtoflags(each_task[4])
                    c = each_task[5]
                    if c == V1_DT_TBD_PLACEHOLDER:
                        c = None
                    else:
                        c = timeutil.strtoutcday(c)
                    task = taskobj.Task(name = n, detail = d, due = u, \
                                        flags = f, complete = c)
                    weighting = self.__calc_task_weighting(objid, task)
                    retlist.append((objid, task, weighting))
                return retlist
            finally:
                cur.close()
        elif objtype is taskobj.Tag:
            cur = self.__db.cursor()
            try:
                cur.execute(V1_SELECT_ALL_TAG)
                found_tags = cur.fetchall()
                for each_tag in found_tags:
                    id = int(each_tag[0])
                    n = each_tag[1]
                    d = each_tag[2]
                    w = int(each_tag[3])
                    tag = taskobj.Tag(name = n, detail = d, weighting = w)
                    retlist.append((id, tag, w))
                return retlist
            finally:
                cur.close()
        else:
            raise errors.TaskObjectError(\
                    'Unknown type: {0}'.format(objtype), \
                    errors.E_TA_WRONGTYPE)
        self.__log.err("list(): Unknown object type {0}".format(objtype))
        return None

    def delete(self, objtype, objid):
        """
        Database.delete(self, objtype, id) -> obj or None

        Delete object from database. The objtype parameter specifies
        what object is expected.

        Return the last copy of selected object. If nothing is found,
        return None, and nothing was performed.

        If given objtype was not recognized, a TaskObjectError is
        thrown.
        """
        obj = self.getbyid(objtype, objid)
        if obj is not None:
            cur = self.__db.cursor()
            try:
                if objtype is taskobj.Task:
                    cur.execute(V1_DELETE_TASK_BY_ID, (objid,))
                elif objtype is taskobj.Tag:
                    cur.execute(V1_DELETE_TAG_BY_ID, (objid,))
                else:
                    raise errors.InternalError(\
                            "delete(): Unknown object in database", \
                            "type = {0}".format(objtype))
                self.__db.commit()
                return obj
            finally:
                cur.close()
        return None

    def update(self, objid, obj):
        """
        Database.update(self, objid, obj): -> None

        Get object with given ID. The objtype parameter specifies what
        object is expected. Usually used after list() function to
        retrieve full information of an object.

        Return the found object, or None if no matched object is found.

        If the given type is not recognized, a TaskObjectError
        exeption is thrown.
        """
        if type(obj) is taskobj.Task:
            cur = self.__db.cursor()
            try:
                cur.execute(V1_SELECT_TASK_BY_ID, (objid,))
                found_data = cur.fetchall()
                if len(found_data) == 1:
                    # That's expected. All object should be unique
                    i = objid
                    n = obj.name
                    d = obj.detail
                    u = timeutil.dttostr(obj.due)
                    f = obj.flagstostr()
                    if obj.complete is not None:
                        c = timeutil.dttostr(obj.complete)
                    else:
                        c = V1_DT_TBD_PLACEHOLDER
                    cur.execute(V1_UPDATE_TASK_BY_ID, (n, d, u, f, c, i))
                    self.__db.commit()
                    return obj
                elif len(found_data) == 0:
                    return None
                else:
                    raise errors.InternalError(\
                            "update(): more than one tasks found", objid)
            finally:
                cur.close()
        elif type(obj) is taskobj.Tag:
            cur = self.__db.cursor()
            try:
                # Renaming a tag to a name used by another tag is not
                # allowed.
                nameid = self.__calc_tag_nameid(obj.name)
                cur.execute(V1_SELECT_TAG_BY_NAMEID, (nameid,))
                found_data = cur.fetchall()
                found_count = len(found_data)
                if found_count == 0:
                    # As expected. We are assigning a new name, which is
                    # never used by any existing object.
                    pass
                elif len(found_data) == 1:
                    found_id = found_data[0][0]
                    if found_id == objid:
                        # As expected. The only object with same name is
                        # what we are updating.
                        pass
                    else:
                        # Assigning a new name used by another tag object.
                        raise errors.TaskObjectError(obj.name, \
                                                 errors.E_TA_SIMILARNAME)
                else:
                    # Internal error. The name has been used by more
                    # than one existing objects.
                    raise errors.InternalError(obj.name,
                                               errors.E_TA_SIMILARNAME)
                # Now we can continue.
                cur.execute(V1_SELECT_TAG_BY_ID, (objid,))
                found_data = cur.fetchall()
                if len(found_data) == 1:
                    i = objid
                    n = obj.name
                    d = obj.detail
                    w = obj.weighting
                    a = nameid
                    cur.execute(V1_UPDATE_TAG_BY_ID, (n,d,w,a,i))
                    self.__db.commit()
                    return obj
                elif len(found_data) == 0:
                    return None
                else:
                    raise errors.InternalError(\
                            "update(): more than one tags found", objid)
            finally:
                cur.close()
        else:
            self.__log.err("update(): Unknown object {0}".format(type(obj)))
            raise errors.TaskObjectError(\
                    'Unknown type: {0}'.format(type(obj)), \
                    errors.E_TA_WRONGTYPE)
        return None

    def assigntag(self, taskid, tagid, assign=True):
        """
        Database.assigntag(taskid, tagid, assign=True) -> tagid or None

        Assign or unassign a tag to given task. Both task ID and tag ID 
        must be defined in database. if parameter assign is set to True,
        the tag is assigned to given task, or, if `assign' is set to
        False, then the tag is unassigned.

        Return tagid if the relationship between task and tag is
        performed, or None if tag or task ID is not defined.
        """
        cur = self.__db.cursor()
        try:
            cur.execute(V1_SELECT_TASK_BY_ID, (taskid,))
            found_tasks = cur.fetchall()
            if len(found_tasks) == 0:
                return None # Object not found
            elif len(found_tasks) == 1: # This is as expected.
                pass
            else:
                raise errors.InternalError(\
                    "assigntag(): more than one tasks found by id", taskid)
            cur.execute(V1_SELECT_TAG_BY_ID, (tagid,))
            found_tags = cur.fetchall()
            if len(found_tags) == 0:
                return None # Object not found
            elif len(found_tags) == 1: # This is as expected.
                pass
            else:
                raise errors.InternalError(\
                        "assigntag(): more than one tasks found", taskid)
            cur.execute(V1_SELECT_TASK_TAG_MAP_BY_ID, (taskid, tagid))
            found_maps = cur.fetchall()
            if len(found_maps) == 0: # This assignment is not found
                if assign:
                    cur.execute(V1_INSERT_TASK_TAG_MAP, (taskid, tagid))
                    self.__db.commit()
                return tagid
            elif len(found_maps) == 1: # This assignment is found
                if not assign:
                    try:
                        cur.execute(V1_DELETE_TASK_TAG_MAP, (taskid, tagid))
                        self.__db.commit()
                    except Exception as e:
                        print e
                return tagid
            else:
                raise errors.InternalError(\
                        "assigntag(): mapping is already found", \
                        "taskid = {0}, tagid = {1}".format(taskid, tagid))
        finally:
            cur.close()
        return None

    def gettagids(self, taskid):
        """
        Database.gettagids(self, taskid) -> list of tag IDs or None

        Get a list of IDs of associated tags, which are assigned to
        given task.

        Return a tuple of tag IDs if found, or empty tuple if the task has
        no tag assigned. If the task ID does not exist, the function
        returns None.
        """
        cur = self.__db.cursor()
        try:
            cur.execute(V1_SELECT_TASK_BY_ID, (taskid,))
            found_tasks = cur.fetchall()
            if len(found_tasks) == 0:
                # The task is not defined.
                return None
            elif len(found_tasks) == 1:
                # This is as expected. We have a task here and start
                # getting assigned tags.
                cur.execute(V1_SELECT_ASSIGNED_TAGS_BY_TASKID, (taskid,))
                found_tagids = cur.fetchall()
                # Returned query result will be something like:
                # [(d1,), (d2,), ...]. They must be reformed.
                return tuple(map(lambda ele: ele[0], found_tagids))
            else:
                raise errors.InternalError(\
                        "gettagids(): more than one task with same ID", \
                        "taskid = {0}".format(taskid))
        finally:
            cur.close()
        return None

    def getbyid(self, objtype, objid):
        """
        Database.getbyid(self, objtype, id) -> object or None 

        Get an object by specified ID.

        Return None if there's no object defined as given ID. If given
        object type is not recognized, a TaskObjectError exception is
        thrown.
        """
        if type(objid) is not type(0):
            raise errors.TaskObjectError(\
                    '[int]objid = {0}'.format(type(objtype)), \
                    errors.E_TA_WRONGTYPE)
        if objtype is taskobj.Task:
            cur = self.__db.cursor()
            try:
                try:
                    cur.execute(V1_SELECT_TASK_DATA_BY_ID, (objid, ))
                except Exception as e:
                    print e
                found_tasks = cur.fetchall()
                if len(found_tasks) == 0:
                    return None
                elif len(found_tasks) == 1: # This is as expected
                    n = found_tasks[0][0]
                    d = found_tasks[0][1]
                    u = timeutil.strtoutcday(found_tasks[0][2])
                    f = taskobj.Task.strtoflags(found_tasks[0][3])
                    c = found_tasks[0][4]
                    if c == V1_DT_TBD_PLACEHOLDER:
                        c = None
                    else:
                        c = timeutil.strtoutcday(c)

                    task = taskobj.Task(name = n, detail = d, due = u, \
                                        flags = f, complete = c)
                    return task
                else:
                    raise errors.InternalError(\
                        "getbyid(): more than one tasks found by id", id)
            finally:
                cur.close()
        elif objtype is taskobj.Tag:
            cur = self.__db.cursor()
            try:
                cur.execute(V1_SELECT_TAG_DATA_BY_ID, (objid, ))
                found_tags = cur.fetchall()
                if len(found_tags) == 0:
                    return None
                elif len(found_tags) == 1: # This is as expected
                    n = found_tags[0][0]
                    d = found_tags[0][1]
                    w = int(found_tags[0][2])

                    tag = taskobj.Tag(name = n, detail = d, weighting = w)
                    return tag
                else:
                    raise errors.InternalError(\
                        "getbyid(): more than one tags found by id", id)
            finally:
                cur.close()
        else:
            raise errors.TaskObjectError(\
                    'Unknown type: {0}'.format(objtype), \
                    errors.E_TA_WRONGTYPE)
        self.__log.err("getbyid(): Unknown object type {0}".format(objtype))
        return None

    def close(self):
        """
        Database.close(self) -> None

        Close database handle. If current database is stored in memory,
        all information will be lost.
        """
        if self.__db is not None:
            self.__db.close()


    def __calc_tag_nameid(self, name):
        """
        Database.__calc_tag_nameid(self, name): -> Refined string

        Generate a refined string from given the name. The genearte
        string removes all spaces make sure all chacters are capital
        letters. The string is used by tag to make there's no two tags
        with same inserted into one database.
        """
        return "".join(name.upper().split(" "))

    def __calc_task_weighting(self, objid, task):
        """
        Database.calc_task_weighting(self, objid, task) -> Weighting value

        Calculate weighting for a task. The most important function in
        tatolib. Used by list() function.

        """

        tags = []
        cur = self.__db.cursor()
        try:
            cur.execute(V1_SELECT_TASK_TAG_MAP_BY_TASKID, (objid,))
            found_maps = cur.fetchall()
            for each_map in found_maps:
                tagid = int(each_map[1])
                # XXX This is a dirty optimization. It avoids
                # unnecessary memory usage for name & detail properties,
                # but requires update when task need more data to
                # calculate weighting.
                cur.execute(V1_SELECT_TAG_WEIGHTING_BY_ID, (tagid,))
                found_tag_data = cur.fetchall()
                if len(found_tag_data) == 1:
                    w = int(found_tag_data[0][0])
                    newtag = taskobj.Tag(name = strutil.u_(""),
                                         detail = strutil.u_(""),
                                         weighting = w)
                    tags.append(newtag)
                else:
                    raise errors.InternalError(\
                            "calc_task_weighting() two tags with same id",\
                            tagid)
        finally:
            cur.close()
        return task.getweighting(tags)

# vim:tw=78:et:sw=4
